''' 班级相关的处理程序 '''
from http.client import responses

from jinja2 import TemplateNotFound
import tornado
from tornado import web, gen
from tornado.httputil import url_concat
from tornado.options import options

import os
import shutil
import json
import logging
import pymysql

from .base import BaseHandler
from utils.dbutil import MySQLConnection

class ClassesHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        user = self.get_current_user()
        self.render('classes.html', user=user)

class ClassHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        search_coursename = self.get_argument('coursename', "")
        search_status = self.get_argument('status', "1")
        search_startdate = self.get_argument("startdate", "")
        search_enddate = self.get_argument("enddate", "")
        search_pageindex = int(self.get_argument("pageindex", 1))
        pagesize = int(self.get_argument("pagesize", 5))
        startindex = (search_pageindex - 1) * pagesize
        endindex = startindex + pagesize

        sql = "select SQL_CALC_FOUND_ROWS a.classid, a.classname, b.courseid, b.coursename, \
            DATE_FORMAT(a.startdate,'%Y-%m-%d') as startdate, DATE_FORMAT(a.enddate,'%Y-%m-%d') as enddate, a.status \
            from class as a inner join course as b on a.courseid=b.courseid where 1 = 1"
        if search_coursename:
            sql = sql + " and b.coursename like '%" + search_coursename + "%'"
        if search_status:
            sql = sql + " and a.status=" + search_status
        if search_startdate:
            sql = sql + " and a.startdate>='" + search_startdate + "'"
        if search_enddate:
            sql = sql + " and a.startdate<='" + search_enddate + "'"
        
        sql = (sql + ' order by classid desc limit {}, {};').format(startindex, endindex)

        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql)
                search_classes = cursor.fetchall()
                sql = 'SELECT FOUND_ROWS() as total'
                cursor.execute(sql)
                search_total = cursor.fetchone()
        finally:
            conn.close()

        self.write(json.dumps({'search_classes': search_classes, 'total':search_total['total']}, ensure_ascii=True))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def post(self):
        result = {'code':'success', 'message':''}
        labclass = json.loads(self.request.body.decode())
        classname = labclass.get('classname')
        courseid = int(labclass.get('courseid'))
        startdate = labclass.get('startdate')
        enddate = labclass.get('enddate')
        status = int(labclass.get('status'))
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.callproc('class_new', args=(classname, courseid, startdate, enddate, status, 0))
                cursor.execute('select @_class_new_5')
                out_params = cursor.fetchone()
                conn.commit()
                classid = out_params['@_class_new_5']
                result['message'] = {'classid':classid}
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'新增实验班级失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def put(self):
        result = {'code':'success', 'message':''}
        labclass = json.loads(self.request.body.decode())
        classid = int(labclass.get('classid'))
        classname = labclass.get('classname')
        courseid = int(labclass.get('courseid'))
        startdate = labclass.get('startdate')
        enddate = labclass.get('enddate')
        status = int(labclass.get('status'))
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "update class set classname='{}', courseid={}, startdate='{}', enddate='{}', status={} where classid={}"
                sql_formatted = sql.format(classname, courseid, startdate, enddate, status, classid)
                cursor.execute(sql_formatted)
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'修改实验班级失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def delete(self):
        result = {'code':'success', 'message':''}

        classid = self.get_argument("id")
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "select status from class where classid={}".format(classid)
                cursor.execute(sql)
                status = cursor.fetchone()
                if status['status'] == 1:                  # 如果该实验班级正在进行中，则不能删除
                    result['code'] = 'error'
                    result['message'] = '无法删除正在进行的实验班级'
                else:                       # 可以删除该实验班级
                    sql = "delete from classstudent where classid=" + classid
                    cursor.execute(sql)
                    sql = "delete from class where classid=" + classid
                    cursor.execute(sql)
                    conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'删除实验课程失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

class ClassStudentsHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        user = self.get_current_user()
        classid = self.get_argument("id")
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "select classid, classname from class where classid={}".format(classid)
                cursor.execute(sql)
                labclass = cursor.fetchone()
        finally:
            conn.close()
        self.render('classstudents.html', user=user, labclass=labclass)



class ClassStudentHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        classid = self.get_argument('id')
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "select a.accountid, a.accountname, a.truename from account as a inner join classstudent as b \
                    on a.accountid=b.accountid where b.classid={}".format(classid)
                cursor.execute(sql)
                accounts = cursor.fetchall()
        finally:
            conn.close()
        
        self.write(json.dumps(accounts, ensure_ascii=True))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def post(self):
        result = {'code':'success', 'message':''}
        data = json.loads(self.request.body.decode())
        classid = data['classid']
        accountids = data['accountids']
        if len(accountids) == 0:
            return
        accountids = accountids.split(',')[0:-1]        # 去掉结尾的逗号
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "select accountid from classstudent where classid={}".format(classid)
                cursor.execute(sql)
                query = cursor.fetchall()
                # 对比待加入的id和原有的id之间的差异，获取原有id中不存在的id值
                existedids = [str(x['accountid']) for x in query]
                newids = [x for x in accountids if x not in existedids]
                
                if len(newids) > 0:
                    sql = "insert into classstudent(classid, accountid) values"
                    for id in newids:
                        sql = sql + "({0},{1}),".format(classid, id)
                    sql = sql[0:-1]
                    cursor.execute(sql)
                    conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'向实验班级中添加学生失败!'
            logging.error(ex)
        finally:
            conn.close()
        self.write(json.dumps(result))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def delete(self):
        result = {'code':'success', 'message':''}
        classid = self.get_argument("classid")
        accountid = self.get_argument("accountid")
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "delete from classstudent where accountid={0} and classid={1}".format(accountid, classid)
                cursor.execute(sql)
                cursor.execute(sql)
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'从班级移除学生失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()



default_handlers = [
    (r'/classes', ClassesHandler),
    (r'/class_classsearch', ClassHandler),
    (r'/class_newclass', ClassHandler),
    (r'/class_editclass', ClassHandler),
    (r'/class_deleteclass', ClassHandler),
    (r'/class_classstudents', ClassStudentsHandler),
    (r'/class_getclassstudents', ClassStudentHandler),
    (r'/class_addstudenttoclass', ClassStudentHandler),
    (r'/class_deleteclassstudent', ClassStudentHandler),
]